package dbUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SQLExecute {
    private Connection connection;
    public SQLExecute(Connection connection){
        this.connection=connection;
    }
    //执行增加，修改，删除操作
    public void executeUpdate(String sql,Object...parames){
        if (sql==null){
            throw new SQLExecteException("not sql");
        }
        if (parames==null){
            throw new SQLExecteException("not parames");
        }
        if (connection==null){
            throw  new SQLExecteException("not conn");
        }
        PreparedStatement ps= null;
        int i = 0;
        try {
            ps = connection.prepareStatement(sql);
            //给sql语句的？赋值
            setParame(ps,parames);
            //执行sql语句并返回 几行数据受影响
            i = ps.executeUpdate();
        } catch (SQLException e) {
            throw new SQLExecteException("executeUpdate failt",e);
        }finally {
            close(ps);
            close(connection);
        }
    }
    //执行查询的操作
    public <T> T executeQuery(String sql,ResultsetHandler<T> handler,Object...parames){
        //判断传过来的参数是否为空
        if (sql==null){
            throw new SQLExecteException("not sql");
        }
        if (handler==null){
            throw new SQLExecteException("not handler");
        }
        if (connection==null){
            throw  new SQLExecteException("not conn");
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        T t=null;
        try {
            ps = connection.prepareStatement(sql);
            //给sql语句的？赋值
            setParame(ps, parames);
            //执行sql语句得到结果集
            rs = ps.executeQuery();
            //把结果集转化到你想要的类型(bean,数组，map对象)
            t =handler.handler(rs);
            //关闭连接
        } catch (SQLException e) {
            throw new SQLExecteException("executeQuery falti",e);
        }finally {
            close(ps);
            close(rs);
            close(connection);
        }

        //返回把结果集转化好的类型(bean,数组，map对象)
        return t;
    }

    //批量操作
    public int[] Batch(String sql,Object[][] param) {
        if (sql==null){
            throw new SQLExecteException("not sql");
        }
        if (param==null){
            throw new SQLExecteException("not parames");
        }
        if (connection==null){
            throw  new SQLExecteException("not conn");
        }
        int[] ints = new int[param.length];
        PreparedStatement ps=null;
        try {
            ps=connection.prepareStatement(sql);
            for (int i=0;i<=param.length;i++){
                setParame(ps,param[i]);
                ps.addBatch();
            }
            ints = ps.executeBatch();
        } catch (SQLException e) {
           throw new SQLExecteException("executeBatch faile",e);
        }finally {
            close(ps);
            close(connection);
        }
        return ints;
    }


    //给sql语句的？赋值
    private void setParame(PreparedStatement ps,Object... parames) {
        for (int i = 0; i < parames.length ; i++) {
            try {
                ps.setObject(i+1,parames[i]);
            } catch (SQLException e) {
                throw new SQLExecteException("Parameters fatil",e);
            }
        }
    }
    //开启事务
    public void beginTranstaction(){
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            throw new SQLExecteException("beginTranstaction fatil",e);
        }
    }
    //提交事务
    public  void commit(){
        try {
            connection.commit();
        } catch (SQLException e) {
            throw new SQLExecteException("commit fatil",e);
        }
    }
    //事务回滚
    public void rollback(){
        try {
            connection.rollback();
        } catch (SQLException e) {
            throw new SQLExecteException("rollback fatil",e);
        }
    }
    //关闭连接
    private void close(Connection connection){
        try {
            connection.close();
        } catch (SQLException e) {
            throw new SQLExecteException("connectionClose fatil",e);
        }
    }
    //关闭PreparedStatement的连接
    private void close(PreparedStatement ps){
        try {
            ps.close();
        } catch (SQLException e) {
            throw new SQLExecteException("PreparedStatementClose fatil",e);
        }
    }
    //关闭结果集的连接
    private void close(ResultSet rs){
        try {
            rs.close();
        } catch (SQLException e) {
            throw new SQLExecteException("ResultSetClose fatil",e);
        }
    }
}
